package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.Orders;
import com.study.manage.domain.enumm.OrdersType;
import com.study.manage.domain.enumm.PayState;

public interface OrdersRepository extends PagingAndSortingRepository<Orders, Long> {

    List<Orders> findByTypeAndExtId(OrdersType type, Long extId);

    @Query("select t from Orders t where (t.orderNo=:orderNo or :orderNo is null)"
            + " and (t.type=:type or :type is null) and (t.userId=:userId or :userId is null)"
            + " and (t.payState=:payState or :payState is null) and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Page<Orders> findByOrderNoAndTypeAndUserIdAndState(@Param("orderNo") String orderNo,
            @Param("type") OrdersType type, @Param("userId") Long userId,
            @Param("payState") PayState state, @Param("date") Date date, Pageable pageable);

    @Query("select sum(t.total) from Orders t where t.payState=:payState and t.evaluateId in (:evaluateIds)"
            + " and (to_days(t.payAt)=to_days(:payAt) or :payAt is null)")
    Float sumTotalByPayStateAndEvaluateIdInAndDate(@Param("payState") PayState payState,
            @Param("evaluateIds") List<Long> evaluateIds, @Param("payAt") Date payAt);


    @Query("select sum(t.total) from Orders t where t.payState=:payState and t.evaluateId in (:evaluateIds)"
            + " and t.type in (:types) and (to_days(t.payAt)=to_days(:payAt) or :payAt is null)")
    Float sumTotalByPayStateAndEvaluateIdInAndTypeAndDate(@Param("payState") PayState payState,
            @Param("evaluateIds") List<Long> evaluateIds, @Param("types") List<OrdersType> types,
            @Param("payAt") Date payAt);

    @Query("select t from Orders t where t.evaluateId=:evaluateId and (t.payState=:payState or :payState is null) and (t.orderNo=:orderNo or :orderNo is null) and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null)"
            + " and  (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null)")
    Page<Orders> findByEvaluateIdAndPayStateAndOrderNoAndDate(@Param("evaluateId") Long evaluateId,
            @Param("payState") PayState payState, @Param("orderNo") String orderNo,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt, Pageable pageable);


    @Query(nativeQuery = true,
            value = "select t.created_at,sum(t.total),'报名收费' as type from sa_orders as t  where t.pay_state=1 and t.typee in (1,3) and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(t.id),'报名成功人数' as type from sa_evaluate_apply t  where t.state=0 and t.verify=1 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.total),'商品收费' as type from sa_orders as t  where t.pay_state=1 and t.typee=0 "
                    + "and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(od.count),'商品售卖数' as type from sa_orders as t"
                    + " right join sa_orders_detail od on od.orders_id=t.id where t.pay_state=1 and t.typee=0 "
                    + "and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(od.count),'投票数' as type from sa_orders as t right join sa_orders_detail as od on od.orders_id=t.id"
                    + " where t.pay_state=1 and t.typee=2 and t.evaluate_id in (:evaluateIds)"
                    + " and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.total),'投票收费' as type from sa_orders as t"
                    + " where t.pay_state=1 and t.typee=2 and t.evaluate_id in (:evaluateIds)"
                    + " and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countByEvaluateIdInGroupByDays(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);



}
